The present invention relates to a user interface, and a method for exercising a user interface, in the context of a data processing system. More particularly, the invention is directed to a user interface and method by which a human user can define and use graphical depictions to link information in a relational database system.
Computer implemented relational databases abound. One example is the Extended Services for Operating System/2 (OS/2) program available from IBM Corporation. Another is the Paradox program available from Borland International. These are but two of numerous relational databases which provide users with tremendous data storage and retrieval potential, but have proven to be quite challenging to learn and efficiently utilize by novices to database technology. The need for friendlier user interfaces is becoming particularly acute given the expanded availability of computerized database systems, the greater number of users of such systems, and the reduced relative experience of such users.
A particularly complex aspect of database organization relates to the creation and use of keys, and particularly the linking of keys between multiple tables to ensure referential integrity. Referential integrity helps enforce data integrity within the database system by setting constraints on the database table values, such that all references from one table to another are valid and accurate. Though the individual formats and functions of relational database primary keys and foreign keys (sometimes referred to as secondary keys) are described in user manuals, the actual relationships which they create within the tables are difficult to visualize in the manner that such keys are routinely used. For example, if a database has two tables of diverse information and uses telephone numbers in each of the tables to identify distinct rows in the tables, and the tables are related by such telephone numbers as a key, referential integrity ensures that the data in one table is not contaminated or destroyed by a change in the other table. Consistency is ensured by linking the tables.
Although the concepts of primary keys, foreign keys, and table indexes have existed for a long time in relational database technology, their efficient creation in a database which provides referential integrity can often be challenging. In general, the primary key is composed of one or more non-null columns which uniquely identify each row in a table. In the context of our earlier example, a column of telephone numbers could serve as a primary key to identify related addresses until such time that the table extended into different area codes. Under such circumstances, the primary key would have to include both the area code column and the local telephone number column. The column or columns defining a foreign key reference a primary key. In the typical situation where the foreign key is in a different table than the primary key, the table possessing the primary key is referred to as the parent table. The value of each foreign key, in our example the combination of area code and local telephone number columns, must match one of the combined values in the associated primary key in the parent table. Thus, a table can have only one primary key, which itself can be composed of multiple columns, but can have a multiplicity of foreign keys related thereto.
Creating primary keys, foreign keys and indexes related thereto, is not only challenging but prone to error when accomplished in the context of present user interfaces for defining tables. The conventional practice of defining tables with keys involves the use of text descriptions. For example, according to the practice in the aforementioned IBM Extended Services for OS/2 Database Manager program, primary keys and foreign keys are defined by specific text statements to that effect. Other commonly used practices, an example being the earlier noted Borland International Paradox program, mandates the placement of asterisks or other symbols adjacent descriptors of the columns during the definition of the table primary keys, and applies the concept of linking through borrowed columns to relate columns in other tables. In either case, the names and properties of the columns which make up the table are specified in text listings of columns and their related properties. Thus, each table is treated separately and has its column names and properties defined by parallel adjacent lists, with table-to-table relationships integrated by text of coded symbol representation.
The user must decipher the links during the creation or subsequent modification by selectively interpreting and conceptually relating information from each of the table representations. This approach has proven to be a significant task for a knowledgeable relational database developer, and a source of confusion and frustrations to novice users. The complexity increases exponentially as the size and number of the tables linked by key increases linearly. Therefore, there exists a need for a user interface to a relational database which provides nominally experienced users with information suitable to understand and create both keys and related indexes for one or more tables, including key arrangements which mandate referential integrity.